/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import dao.GetCnn;
import entity.Customer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author quanghv
 */
public class CustomerModel {

    public List<Customer> getListCustomer(int begin_number_rows, int end_number_rows) throws SQLException {
        List<Customer> list = new ArrayList<Customer>();
        String select = "SELECT [username],[password],[name],[city], CONVERT(VARCHAR(10), [birthday], 101) as bithday, [address],"
                + "[phone], [email], [description]"
                + "FROM   (SELECT ROW_NUMBER() OVER(ORDER BY username) AS rownumber,* FROM customer)"
                + "as vt_table  WHERE  rownumber >= " + begin_number_rows + " AND rownumber <= " + end_number_rows;
        Connection cnn = GetCnn.getCnn();
        if (cnn != null) {
            Statement st = cnn.createStatement();
            Customer entity = null;
            ResultSet rs = st.executeQuery(select);
            while (rs.next()) {
                entity = new Customer();
                entity.setUsername(rs.getString("username"));
                entity.setPassword(rs.getString("password"));
                entity.setCity(rs.getString("city"));
                entity.setName(rs.getString("name"));
                entity.setAddress(rs.getString("address"));
                entity.setEmail(rs.getString("email"));
                entity.setPhone(rs.getString("phone"));
                entity.setDescription(rs.getString("description"));

                list.add(entity);
            }
        }
        return list;
    }

    public int getTotalRows() throws SQLException {
        String select = "SELECT [username] FROM customer";
        Connection cnn = GetCnn.getCnn();
        int count = 0;
        if (cnn != null) {
            Statement st = cnn.createStatement();
            ResultSet rs = st.executeQuery(select);
            while (rs.next()) {
                count++;
            }
        }
        return count;
    }
}
